package org.wu.framework.lazy.orm.database.sql.util;

import org.wu.framework.core.NormalUsedString;
import org.wu.framework.core.exception.RuntimeExceptionFactory;
import org.wu.framework.core.utils.ObjectUtils;
import org.wu.framework.core.utils.StringUtils;
import org.wu.framework.lazy.orm.core.persistence.conf.LazyTableEndpoint;
import org.wu.framework.lazy.orm.core.source.adapter.SourceFactory;
import org.wu.framework.lazy.orm.database.lambda.domain.Persistence;
import org.wu.framework.lazy.orm.database.lambda.stream.LambdaTableType;
import org.wu.framework.lazy.orm.database.lambda.stream.condition.part.Condition;
import org.wu.framework.lazy.orm.database.lambda.stream.condition.part.SqlPart;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentMap;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * description sql 解析工具
 *
 * @author 吴佳伟
 * @date 2023/01/16 23:49
 */
public class SqlUtils {


    /**
     * 通过错误信息 获取表
     *
     * @param errorMessage Table 'acw1.access_token' doesn't exist
     * @return tableName
     */
    private static String errorMessageWithTable(String errorMessage) {
        if (errorMessage.startsWith("Table") && errorMessage.endsWith("doesn't exist")) {
            return errorMessage
                    .replaceFirst("Table", "")
                    .replace("doesn't exist", "")
                    .replaceAll("'", "");
        } else {
            return null;
        }

    }

    /**
     * description: 解析insert sql中的table信息
     *
     * @param insertSql
     * @return
     * @author 吴佳伟
     * @date: 17.1.23 00:03
     */
    private static List<String> insertTable(String insertSql) {
        List<String> tableNames = new ArrayList<>();

        // insert into table (xx,x) values(1,2)
        // 最大命中数
        // 命中截止数
        int hitNum = 0;
        String[] sqlWords = insertSql
                .replaceAll(NormalUsedString.BACKTICK, NormalUsedString.EMPTY)
                .replaceAll(NormalUsedString.NEWLINE, NormalUsedString.SPACE)
                .split(NormalUsedString.SPACE);
        for (String sqlWord : sqlWords) {
            // 空字符串
            if (ObjectUtils.isEmpty(sqlWord)) {
                continue;
            }
            //insert
            if (sqlWord.equalsIgnoreCase("INSERT")) {
                hitNum++;
                continue;
            }
            // into
            if (sqlWord.equalsIgnoreCase("INTO")) {
                hitNum++;
                continue;
            }
            // 命中两次获取临时表
            if (hitNum == 2) {
                tableNames.add(sqlWord);
                hitNum++;
                continue;
            }
        }
        return tableNames;
    }

    /**
     * description: 解析update sql中table信息
     * <p>
     * UPDATE product p, product_price pp SET pp.price = p.price * 0.8 WHERE p.productid= pp.productId;
     * UPDATE product p INNER JOIN product_price pp ON p.productid= pp.productid SET pp.price = p.price * 0.8;
     * UPDATE product p LEFT JOIN product_price pp ON p.productid= pp.productid SET p.isdelete = 1 WHERE pp.productid IS NULL;
     * </p>
     *
     * @param updateSql
     * @return
     * @author 吴佳伟
     * @date: 17.1.23 00:04
     */
    private static List<String> updateTable(String updateSql) {
        List<String> tableNames = new ArrayList<>();
        // 命中截止数
        int hitNum = 0;
        // 命中忽略数
        int hitIgnoreNum = 0;
        String[] sqlWords = updateSql
                .replaceAll(NormalUsedString.BACKTICK, NormalUsedString.EMPTY)
                .replaceAll(NormalUsedString.NEWLINE, NormalUsedString.SPACE)
                .split(NormalUsedString.SPACE);
        for (String sqlWord : sqlWords) {
            // 空字符串
            if (ObjectUtils.isEmpty(sqlWord)) {
                continue;
            }

            if (sqlWord.equalsIgnoreCase("ON")) {
                hitIgnoreNum = 1;
                continue;
            }
            if (hitIgnoreNum == 1 && sqlWord.equalsIgnoreCase("DUPLICATE")) {
                hitIgnoreNum = 2;
                continue;
            }
            if (hitIgnoreNum == 2 && sqlWord.equalsIgnoreCase("KEY")) {
                hitIgnoreNum = 3;
                continue;
            }
            if (hitIgnoreNum == 3 && sqlWord.equalsIgnoreCase("UPDATE")) {
                hitIgnoreNum = 0;
                continue;
            }

            //UPDATE
            if (sqlWord.equalsIgnoreCase("UPDATE")) {
                hitNum = 1;
                continue;
            }
            // 命中一次获取临时表
            if (hitNum == 1) {
                tableNames.add(sqlWord);
                hitNum++;
                continue;
            }
            // 命中两次后遇到,
            if (hitNum == 2 && (sqlWord.equalsIgnoreCase(",") || sqlWord.contains(",")) && !sqlWord.contains("=")) {
                hitNum = 1;
                continue;
            }

            // 命中两次后遇到 JOIN
            if (hitNum == 2 && sqlWord.equalsIgnoreCase("JOIN")) {
                hitNum = 1;
                continue;
            }
        }
        return tableNames;
    }

    /**
     * description: 解析select sql中的table信息
     * <p>
     * select * from students,course ;
     * select * from students inner join course;
     * select * from (select * from table) as temp
     *
     * </p>
     *
     * @param selectSql
     * @return
     * @author 吴佳伟
     * @date: 17.1.23 00:05
     */
    private static List<String> selectTable(String selectSql) {
        List<String> tableNames = new ArrayList<>();
        // 命中截止数
        int hitNum = 0;
        String[] sqlWords = selectSql
                .replaceAll(NormalUsedString.BACKTICK, NormalUsedString.EMPTY)
                .replaceAll(NormalUsedString.NEWLINE, NormalUsedString.SPACE)
                .split(NormalUsedString.SPACE);
        for (String sqlWord : sqlWords) {
            // 空字符串
            if (ObjectUtils.isEmpty(sqlWord)) {
                continue;
            }
            //select
            if (sqlWord.equalsIgnoreCase("select")) {
                hitNum = 1;
                continue;
            }
            // from
            if (sqlWord.equalsIgnoreCase("from")) {
                hitNum = 2;
                continue;
            }
            // 命中两次遇到(
            if (hitNum == 2 && sqlWord.equalsIgnoreCase("(")) {
                hitNum = 0;
                continue;
            }
            // 命中两次遇到(select
            if (hitNum == 2 && sqlWord.equalsIgnoreCase("(select")) {
                hitNum = 1;
                continue;
            }
            // 命中两次获取临时表
            if (hitNum == 2) {
                tableNames.add(sqlWord);
                hitNum = 3;
                continue;
            }
            // 命中三次已经取数据
            if (hitNum == 3 && (sqlWord.equalsIgnoreCase(",") || sqlWord.equalsIgnoreCase("join"))) {
                hitNum = 2;
                continue;
            }
        }
        return tableNames;
    }

    /**
     * description:  根据sql 解析出schema
     *
     * <p>
     * Failed to obtain JDBC Connection; nested exception is java.sql.SQLSyntaxErrorException: Unknown database 'acw'
     * </p>
     *
     * @param sql
     * @return
     * @author 吴佳伟
     * @date: 8.2.23 18:24
     */
    public static List<String> schema(String sql) {
        //show tables from demo
        List<String> schemas = new ArrayList<>();
        // 命中截止数
        int hitNum = 0;
        String[] sqlWords = sql
                .replaceAll(NormalUsedString.BACKTICK, NormalUsedString.EMPTY)
                .replaceAll(NormalUsedString.NEWLINE, NormalUsedString.SPACE)
                .split(NormalUsedString.SPACE);
        for (String sqlWord : sqlWords) {
            // 空字符串
            if (ObjectUtils.isEmpty(sqlWord)) {
                continue;
            }
            // from
            if (sqlWord.equalsIgnoreCase("from")) {
                hitNum = 2;
                continue;
            }
            // Unknown database
            if (sqlWord.equalsIgnoreCase("Unknown")) {
                hitNum = 1;
                continue;
            }
            if (hitNum == 1 && sqlWord.equalsIgnoreCase("database")) {
                hitNum = 2;
                continue;
            }
            if (hitNum == 2) {
                // 添加数据库
                if (sqlWord.contains(NormalUsedString.DOT)) {
                    String[] split = sqlWord.split("\\.");
                    schemas.add(split[0].replaceAll(NormalUsedString.SINGLE_QUOTE, NormalUsedString.EMPTY));
                } else {
                    schemas.add(sqlWord.replaceAll(NormalUsedString.SINGLE_QUOTE, NormalUsedString.EMPTY));
                }

            }
        }
        return schemas;
    }

    /**
     * description: 获取sql中表名称
     *
     * @param sql
     * @return
     * @author 吴佳伟
     * @date: 17.1.23 19:14
     */
    public static List<String> tablesInSql(String sql) {
        List<String> insertTable = insertTable(sql);
        List<String> updateTable = updateTable(sql);
        List<String> selectTable = selectTable(sql);
        String errorTable = errorMessageWithTable(sql);

        List<String> tableNames = new ArrayList<>();
        if (errorTable != null) {
            tableNames.add(errorTable);
        }
        tableNames.addAll(insertTable);
        tableNames.addAll(updateTable);
        tableNames.addAll(selectTable);
        return tableNames;
    }

    /**
     * description: 获取sql中表名称
     *
     * @param sqlList sql
     * @return
     * @author 吴佳伟
     * @date: 17.1.23 19:14
     */
    public static List<String> tablesInSql(List<String> sqlList) {
        List<String> tableNames = new ArrayList<>();
        for (String sql : sqlList) {
            List<String> tablesInSql = tablesInSql(sql);
            tableNames.addAll(tablesInSql);
        }
        return tableNames;
    }

    /**
     * 将sql中的条件判断解析出来
     *
     * @param sql 原始sql
     * @return 解析后条件判断
     */

    public static String whereConditionsInSql(String sql) {
        String sqlUpperCase = sql.toUpperCase();
        if (sqlUpperCase.contains(NormalUsedString.WHERE)) {
            return sql.substring(sqlUpperCase.lastIndexOf(NormalUsedString.WHERE));
        } else {
            return "";
        }
    }

    /**
     * 将sql中的数据解析成map对象
     *
     * @param sql set id=1 ,name ="xiaoming" where id=1
     * @return
     */

    public static Map<String, Object> setDataInSql(String sql) {
        String sqlUpperCase = sql.toUpperCase();

        Map<String, Object> data = new HashMap<>();

        String substring = sql
                .substring(sqlUpperCase.indexOf(NormalUsedString.SET) + 3, sqlUpperCase.contains(NormalUsedString.WHERE) ? sqlUpperCase.lastIndexOf(NormalUsedString.WHERE) : sqlUpperCase.length());

        for (String split : substring.split(NormalUsedString.COMMA)) {
            if (split.contains(NormalUsedString.EQUALS)) {
                String[] kv = split.split(NormalUsedString.EQUALS);
                String k = kv[0];
                String v = kv[1];
                data.put(k.replaceAll(NormalUsedString.SPACE, NormalUsedString.EMPTY), v.replaceAll(NormalUsedString.SPACE, NormalUsedString.EMPTY));
            }
        }
        return data;
    }

    /**
     * @param sql <p>
     *            insert into user(id, name, age) values(?,?,?)
     *            </p>
     * @return
     */
    public static List<Map<String, Object>> insertDataInSql(String sql) {
        // 去除空格
        List<Map<String, Object>> insertData = new ArrayList<>();
        String upperCase = sql.toUpperCase();

        // 解析 insert into user(id, name, age) values(?,?,?)
        if (upperCase.contains(NormalUsedString.INSERT)) {

            String keyString = sql.substring(upperCase.indexOf(NormalUsedString.LEFT_BRACKET) + 1,
                    upperCase.indexOf(NormalUsedString.RIGHT_BRACKET));

            String[] keyList = keyString.split(NormalUsedString.COMMA);

            String valuesString = sql.substring(
                    upperCase.indexOf(NormalUsedString.VALUES) + NormalUsedString.VALUES.length(),
                    upperCase.contains(NormalUsedString.ON_DUPLICATE_KEY_UPDATE) ?
                            upperCase.indexOf(NormalUsedString.ON_DUPLICATE_KEY_UPDATE) :
                            upperCase.length()
            );


            // 替换第一个(
            valuesString = valuesString.replaceFirst("\\(", "");
            String[] valueItemList = null;


            if (valuesString.contains(NormalUsedString.RIGHT_BRACKET + NormalUsedString.COMMA)) {
                String regex = NormalUsedString.BACK_SLASH + NormalUsedString.RIGHT_BRACKET + NormalUsedString.COMMA;
                valuesString = valuesString
                        .replaceAll(NormalUsedString.BACK_SLASH + NormalUsedString.LEFT_BRACKET, NormalUsedString.EMPTY);
                valueItemList = valuesString.substring(0, valuesString.lastIndexOf(NormalUsedString.RIGHT_BRACKET)).split(regex);
            } else {
                // 单个记录
                valueItemList = new String[1];
                valueItemList[0] = valuesString.substring(0, valuesString.lastIndexOf(NormalUsedString.RIGHT_BRACKET));
            }

            for (String valueItem : valueItemList) {
                Map<String, Object> dataItem = new HashMap<>();
                String[] valueList = valueItem.split(NormalUsedString.COMMA);
                for (int i = 0; i < keyList.length; i++) {
                    String value = valueList[i];
                    String cleanedValue = value.replaceAll(NormalUsedString.SPACE, NormalUsedString.EMPTY);
                    if (cleanedValue.startsWith(NormalUsedString.SINGLE_QUOTE) && cleanedValue.endsWith(NormalUsedString.SINGLE_QUOTE)) {
                        cleanedValue = cleanedValue.substring(1, cleanedValue.length() - 1);
                    }
                    String key = keyList[i];
                    dataItem.put(key.replaceAll(NormalUsedString.SPACE, NormalUsedString.EMPTY), cleanedValue);
                }
                insertData.add(dataItem);
            }


        }
        return insertData;
    }

    /**
     * 将sql中的数据解析成map对象
     *
     * @param sql <p>
     *            update user set age=18 where id=1
     *            insert into user set age=18
     *            insert into user(id, name, age) values(?,?,?)
     *
     *            </p>
     * @return
     */

    public static List<Map<String, Object>> dataInSql(String sql) {
        String upperCase = sql.toUpperCase();
        if (upperCase.contains(NormalUsedString.INSERT)) {
            return insertDataInSql(sql);
        }
        Map<String, Object> data = setDataInSql(sql);
        return List.of(data);
    }


    /**
     * 将sql中的数据解析成map对象
     *
     * @param sql <p>
     *            update user set age=18 where id=1
     *            insert into user set age=18
     *            insert into user(id, name, age) values(?,?,?)
     *
     *            </p>
     * @return
     */

    public static SqlPart createSqlPartFromSql(String sql) {
        SqlPart sqlPart = new SqlPart();
        Persistence.ExecutionEnum executionEnum = SqlColumnUtils.executionEnumInSql(sql);
        sqlPart.setExecutionEnum(executionEnum);


        List<String> tables = tablesInSql(sql);
        String tableName = tables.get(0);
        sqlPart.setPrimaryTable(tableName);
        ConcurrentMap<Class<?>, LazyTableEndpoint<?>> classTableCache = SourceFactory.getClassTableCache();
        if (classTableCache.values().stream().anyMatch(lazyTableEndpoint -> lazyTableEndpoint.getTableName().equals(tableName))) {
            LazyTableEndpoint<?> lazyTableEndpoint = classTableCache
                    .values()
                    .stream()
                    .filter(l -> l.getTableName().equals(tableName))
                    .findFirst().get();

            sqlPart.setPrimaryClass(lazyTableEndpoint.getClazz());
        }

        // select
        // update
        // insert
        switch(executionEnum) {
            case INSERT,UPSERT:
                List<Map<String, Object>> insertDataInSql = insertDataInSql(sql);
                List<HashMap<String, Object>> hashMaps = insertDataInSql.stream().map(HashMap::new).toList();
                sqlPart.setInsert(hashMaps);
            case SELECT:
                String s = whereConditionsInSql(sql);

            case UPDATE:
                List<Condition> updateSetColumn = SqlColumnUtils.updateSetColumn(sql);
                List<Condition> updateWhereConditions = SqlColumnUtils.updateConditionColumn(sql);
                sqlPart.setUpdateSet(updateSetColumn.stream().collect(Collectors.toMap(c->c.getRowName().toString(),Condition::getRowValue)));
                sqlPart.setConditionType(NormalUsedString.SPACE+NormalUsedString.WHERE+NormalUsedString.SPACE);
                for (Condition updateWhereCondition : updateWhereConditions) {
                    sqlPart.put(updateWhereCondition.getRowName(),updateWhereCondition.getType(),updateWhereCondition.getRowValue());
                }
            case DELETE:
        }




        return sqlPart;
    }
}

